﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;
using System.Data.SqlClient;
using System.Configuration;
using PsychveyApp1.classes;

namespace PsychveyApp1.classes
{
    public class DBManager
    {

        public static UserAccount UserLogin(string username, string password)
        {
            UserAccount useraccount= new UserAccount();
            useraccount.username = "";

                    SqlConnection conn = null;
                    try
                    {
                        conn = new SqlConnection();
                        conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                        conn.Open();
                        SqlCommand comm = new SqlCommand();
                        comm.Connection = conn;
                        comm.CommandText = "SELECT * FROM useraccount WHERE Username = @Username and Password = @Password";
                        comm.Parameters.AddWithValue("@Username", username);
                        comm.Parameters.AddWithValue("@Password", password);
                        SqlDataReader dr = comm.ExecuteReader();

                        while (dr.Read())
                        {
                            useraccount.userID = (string)dr["UserID"];
                            useraccount.username = (string)dr["Username"];
                            useraccount.password = (string)dr["Password"];
                            useraccount.retypePassword = (string)dr["RetypePassword"];
                            useraccount.email = (string)dr["Email"];
                            useraccount.expirationDateForPremium = (string)dr["ExpirationDateForPremium"];
                            useraccount.accountTypeSelected = (string)dr["AccountTypeSelected"];
                            useraccount.paymentStatus = (string)dr["PaymentStatus"];
                            useraccount.listOfSurveys = (string)dr["ListOfSurveys"];
                            useraccount.userFullName = (string)dr["UserFullName"];
                            useraccount.nric = (string)dr["NRIC"];
                            useraccount.paymentID = (string)dr["PaymentID"];
                            useraccount.receiptID = (string)dr["ReceiptID"];
                            useraccount.planID = (string)dr["PlanID"];

                            
                        }//end while
                        dr.Close();
                        conn.Close();
                       
                    }
                    catch (SqlException exception)
                    {
                        throw exception;
                    }
                    return useraccount;
        }

        public static UserAccount GetUserById(string userID)
        {
            UserAccount useraccount = new UserAccount();
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from useraccount WHERE UserID=@UserID";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    useraccount.userID = (string)dr["UserID"];
                    useraccount.username = (string)dr["Username"];
                    useraccount.password = (string)dr["Password"];
                    useraccount.retypePassword = (string)dr["RetypePassword"];
                    useraccount.email = (string)dr["Email"];
                    useraccount.expirationDateForPremium = (string)dr["ExpirationDateForPremium"];
                    useraccount.accountTypeSelected = (string)dr["AccountTypeSelected"];
                    useraccount.paymentStatus = (string)dr["PaymentStatus"];
                    useraccount.listOfSurveys = (string)dr["ListOfSurveys"];
                    useraccount.userFullName = (string)dr["UserFullName"];
                    useraccount.nric = (string)dr["NRIC"];
                    useraccount.paymentID = (string)dr["PaymentID"];
                    useraccount.receiptID = (string)dr["ReceiptID"];
                    useraccount.planID = (string)dr["PlanID"]; 
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return useraccount;
        }

        public static int InsertUser(UserAccount user)
        {
            int rowsinserted = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["PsychAppDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO useraccount(userid,username,password,retypepassword,email,expirationdateforpremium,accounttypeselected,paymentstatus,listofsurveys,userfullname,nric,paymentid,receiptid,planid)" +
                    " VALUES (@userID,@userName,@password,@retypePassword,@email,@expirationDateForPremium,@accountTypeSelected,@paymentStatus,@listOfSurveys,@userFullName,@nric,@paymentID,@receiptID,@planID)";
                comm.Parameters.AddWithValue("@UserID", user.userID);
                comm.Parameters.AddWithValue("@Username", user.username);
                comm.Parameters.AddWithValue("@Password", user.password);
                comm.Parameters.AddWithValue("@RetypePassword", user.retypePassword);
                comm.Parameters.AddWithValue("@Email", user.email);
                comm.Parameters.AddWithValue("@ExpirationDateForPremium", user.expirationDateForPremium);
                comm.Parameters.AddWithValue("@AccountTypeSelected", user.accountTypeSelected);
                comm.Parameters.AddWithValue("@PaymentStatus", user.paymentStatus);
                comm.Parameters.AddWithValue("@ListOfSurveys", user.listOfSurveys);
                comm.Parameters.AddWithValue("@UserFullName", user.userFullName);
                comm.Parameters.AddWithValue("@NRIC", user.nric);
                comm.Parameters.AddWithValue("@PaymentID", user.paymentID);
                comm.Parameters.AddWithValue("@ReceiptID", user.receiptID);
                comm.Parameters.AddWithValue("@PlanID", user.planID);
                rowsinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsinserted;
        }
    }
}